/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     9/30/2014 9:53:45 PM                         */
/*==============================================================*/
/* sua */

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('HOC') and o.name = 'FK_HOC_HOC_SINHVIEN')
alter table HOC
   drop constraint FK_HOC_HOC_SINHVIEN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('HOC') and o.name = 'FK_HOC_HOC2_MONHOC')
alter table HOC
   drop constraint FK_HOC_HOC2_MONHOC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SINHVIEN') and o.name = 'FK_SINHVIEN_RELATIONS_LOPHOC')
alter table SINHVIEN
   drop constraint FK_SINHVIEN_RELATIONS_LOPHOC
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('HOC')
            and   name  = 'HOC2_FK'
            and   indid > 0
            and   indid < 255)
   drop index HOC.HOC2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('HOC')
            and   name  = 'HOC_FK'
            and   indid > 0
            and   indid < 255)
   drop index HOC.HOC_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('HOC')
            and   type = 'U')
   drop table HOC
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LOPHOC')
            and   type = 'U')
   drop table LOPHOC
go

if exists (select 1
            from  sysobjects
           where  id = object_id('MONHOC')
            and   type = 'U')
   drop table MONHOC
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('SINHVIEN')
            and   name  = 'RELATIONSHIP_1_FK'
            and   indid > 0
            and   indid < 255)
   drop index SINHVIEN.RELATIONSHIP_1_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SINHVIEN')
            and   type = 'U')
   drop table SINHVIEN
go

/*==============================================================*/
/* Table: HOC                                                   */
/*==============================================================*/
create table HOC (
   MASV                 char(10)             not null,
   MAMON                nnnvarchar(5)           not null,
   DIEMTHI              float                null,
   constraint PK_HOC primary key (MASV, MAMON)
)
go

/*==============================================================*/
/* Index: HOC_FK                                                */
/*==============================================================*/
create index HOC_FK on HOC (
MASV ASC
)
go

/*==============================================================*/
/* Index: HOC2_FK                                               */
/*==============================================================*/
create index HOC2_FK on HOC (
MAMON ASC
)
go

/*==============================================================*/
/* Table: LOPHOC                                                */
/*==============================================================*/
create table LOPHOC (
   MALOP                nnnvarchar(10)          not null,
   TENLOP               nnnvarchar(50)          null,
   constraint PK_LOPHOC primary key nonclustered (MALOP)
)
go

/*==============================================================*/
/* Table: MONHOC                                                */
/*==============================================================*/
create table MONHOC (
   MAMON                nnnvarchar(5)           not null,
   TENMON               nnnvarchar(50)          null,
   constraint PK_MONHOC primary key nonclustered (MAMON)
)
go

/*==============================================================*/
/* Table: SINHVIEN                                              */
/*==============================================================*/
create table SINHVIEN (
   MASV                 char(10)             not null,
   MALOP                nnnvarchar(10)          not null,
   HOTEN                nnnvarchar(50)          null,
   NGAYSINH             datetime             null,
   constraint PK_SINHVIEN primary key nonclustered (MASV)
)
go

/*==============================================================*/
/* Index: RELATIONSHIP_1_FK                                     */
/*==============================================================*/
create index RELATIONSHIP_1_FK on SINHVIEN (
MALOP ASC
)
go

alter table HOC
   add constraint FK_HOC_HOC_SINHVIEN foreign key (MASV)
      references SINHVIEN (MASV)
go

alter table HOC
   add constraint FK_HOC_HOC2_MONHOC foreign key (MAMON)
      references MONHOC (MAMON)
go

alter table SINHVIEN
   add constraint FK_SINHVIEN_RELATIONS_LOPHOC foreign key (MALOP)
      references LOPHOC (MALOP)
go

